package com.company.planner.service;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.springframework.stereotype.Service;

import java.io.File;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;

@Service(ExcelService.NAME)
public class ExcelServiceBean implements ExcelService {

    private static final Logger log = org.slf4j.LoggerFactory.getLogger(ExcelServiceBean.class);

    @Override
    public String[][] readExcelFile(File file, String sheetName, String startColl, String endColl) throws Exception {
        int startRow = Integer.parseInt(startColl.substring(1)) - 1;
        int endRow = Integer.parseInt(endColl.substring(1)) - 1;
        int starRank = (int) startColl.charAt(0) - 65;
        int endRank = (int) endColl.charAt(0) - 65;
        return readExcelFile(file, sheetName, startRow, starRank, endRow, endRank);
    }

    @Override
    public String[][] readExcelFile(File file, String sheetName, int startRow, int starRank, int endRow, int endRank) throws Exception {
        Workbook workbook = WorkbookFactory.create(file);
        String[][] result = new String[endRow - startRow + 1][endRank - starRank + 1];
        Sheet sheet = workbook.getSheet(sheetName);
//        if(null == sheet){
//            throw new Exception("文件选择错误或者页签被修改！");
//        }
        // 遍历范围内的单元格
        for (int row = startRow; row < endRow + 1; row++) {
            String[] collStrs = new String[endRank - starRank + 1];
            for (int rank = starRank, index = 0; rank < endRank + 1; rank++, index++) {
                collStrs[index] = sheet.getRow(row).getCell(rank) != null ? sheet.getRow(row).getCell(rank).toString() : "";
            }
            result[row - startRow] = collStrs;
        }
        // 判断合并单元格的情况
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (firstRow >= startRow && firstColumn >= starRank && lastRow <= endRow && lastColumn <= endRank) {
                log.info("mergeRegions:(" + firstRow + "," + firstColumn + ")(" + lastRow + "," + lastColumn + ")");
                for (int mergeRow = firstRow; mergeRow <= lastRow; mergeRow++) {
                    for (int mergeLolumn = firstColumn; mergeLolumn <= lastColumn; mergeLolumn++) {
                        result[mergeRow - startRow][mergeLolumn - starRank] = result[firstRow - startRow][firstColumn - starRank];
                    }
                }
            }
        }
        workbook.close();
        return result;
    }

    @Override
    public Integer getLastColl(File file, String sheetName, int startColumn, int beginRow) throws Exception {
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheet(sheetName);
        log.info("sheet:{}", sheet);
        if (null == sheet) {
            throw new Exception("文件选择错误或者页签被修改！");
        }
        // 遍历行
        for (int row = beginRow; row <= sheet.getLastRowNum(); row++) {
            Cell cell = sheet.getRow(row).getCell(startColumn);
            if (null == cell && row != beginRow) {
                return row - 1;
            }
            if (row == sheet.getLastRowNum() && null != cell) {
                return row;
            }
        }
        return null;
    }

    @Override
    public void logText(String[][] result) {
        for (String[] strings : result) {
            for (int i = 0; i < strings.length; i++) {
                if (i == 0) {
                    log.info("| ");
                }
                log.info(strings[i] + " |");
            }
            log.info("");
        }
    }

    @Override
    public Date getDateFormatValue(String strDate) {
        SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy", Locale.CHINESE);
        SimpleDateFormat outputFormat = new SimpleDateFormat("yyyy-MM-dd", Locale.US);
        try {
            Date date = formatter.parse(strDate);
            String format = outputFormat.format(date);
            return outputFormat.parse(format);
        } catch (ParseException e) {
            log.error("Error", e);
        }
        return null;
    }
}